/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package wargame.dao;

import connectionmanager.DB;
import java.util.ArrayList;
import wargame.entity.OutsourceProject;
import wargame.entity.Player;

/**
 *
 * @author Clarence
 */
public class OutsourceProjectDAO {

    public static OutsourceProject retrieveOutsourceProject(int outsource_project_id) {
        String sql = "SELECT `outsource_project_id`, `project_id`, `man_hours_dot_net`, "
                + "`man_hours_c_sharp`, `man_hours_jsp`, `man_hours_php`, `man_hours_ruby`, "
                + "`man_hours_mysql`, `man_hours_mssql`, `man_hours_oracle`, `price`, "
                + "`due_turn`, `offered_by`, `taken_by`, `listing_turn_remaining`, `submitted`, `track_record_point` "
                + "FROM `outsource_project` WHERE outsource_project_id = '" + outsource_project_id + "'";
        DB db = new DB();
        db.connect();
        db.query(sql);
        OutsourceProject op = null;
        if (db.next()) {
            op = populate(db);
        }
        db.close();
        return op;
    }

    private static OutsourceProject populate(DB db) {
        OutsourceProject op = new OutsourceProject();
        op.setProject_id(db.getInt("outsource_project_id"));
        op.setParent_id(db.getInt("project_id"));
        op.setMan_hours_dot_net(db.getInt("man_hours_dot_net"));
        op.setMan_hours_c_sharp(db.getInt("man_hours_c_sharp"));
        op.setMan_hours_jsp(db.getInt("man_hours_jsp"));
        op.setMan_hours_php(db.getInt("man_hours_php"));
        op.setMan_hours_ruby(db.getInt("man_hours_ruby"));
        op.setMan_hours_mysql(db.getInt("man_hours_mysql"));
        op.setMan_hours_mssql(db.getInt("man_hours_mssql"));
        op.setMan_hours_oracle(db.getInt("man_hours_oracle"));
        op.setListing_turn_remaining(db.getInt("listing_turn_remaining"));
        op.setBidded_price(db.getInt("price"));
        op.setDue_turn(db.getInt("due_turn"));
        op.setOffered_by(db.getInt("offered_by"));
        op.setTaken_by(db.getInt("taken_by"));
        op.setSubmitted(db.getInt("submitted"));
        op.setTrack_record_point(db.getInt("track_record_point"));
        Player player = PlayerDAO.retrievePlayer(op.getOffered_by());
        op.setCreator(player);
        return op;
    }

    public static int insertOutsourceProject(OutsourceProject op) {
        String sql = "INSERT INTO outsource_project (`project_id`, `man_hours_dot_net`, "
                + "`man_hours_c_sharp`, `man_hours_jsp`, `man_hours_php`, `man_hours_ruby`, "
                + "`man_hours_mysql`, `man_hours_mssql`, `man_hours_oracle`, `listing_turn_remaining`, "
                + "`price`, `due_turn`, `offered_by`, `taken_by`, `submitted`, `track_record_point`) VALUES ('"
                + op.getParent_id() + "', '" + op.getMan_hours_dot_net() + "', '" + op.getMan_hours_c_sharp() + "', '"
                + op.getMan_hours_jsp() + "', '" + op.getMan_hours_php() + "', '" + op.getMan_hours_ruby() + "', '"
                + op.getMan_hours_mysql() + "', '" + op.getMan_hours_mssql() + "', '" + op.getMan_hours_oracle()
                + "', '" + op.getListing_turn_remaining() + "', '" + op.getBidded_price() + "', '" + op.getDue_turn()
                + "', '" + op.getOffered_by() + "', '" + op.getTaken_by() + "', '" + op.getSubmitted() + "', '"
                + op.getTrack_record_point() + "');";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        int lastInsertedId = db.getLastInsertedID();
        db.close();
        return lastInsertedId;
    }

    public static void updateOutsourceProject(OutsourceProject op) {
        String sql = "UPDATE outsource_project SET `project_id` = '" + op.getParent_id()
                + "', `man_hours_dot_net` = '" + op.getMan_hours_dot_net()
                + "', `man_hours_c_sharp` = '" + op.getMan_hours_c_sharp()
                + "', `man_hours_jsp` = '" + op.getMan_hours_jsp()
                + "', `man_hours_php` = '" + op.getMan_hours_php()
                + "', `man_hours_ruby` = '" + op.getMan_hours_ruby()
                + "', `man_hours_mysql` = '" + op.getMan_hours_mysql()
                + "', `man_hours_mssql` = '" + op.getMan_hours_mssql()
                + "', `man_hours_oracle` = '" + op.getMan_hours_oracle()
                + "', `listing_turn_remaining` = '" + op.getListing_turn_remaining()
                + "', `price` = '" + op.getBidded_price()
                + "', `due_turn` = '" + op.getDue_turn()
                + "', `offered_by` = '" + op.getOffered_by()
                + "', `taken_by` = '" + op.getTaken_by()
                + "', `submitted` = '" + op.getSubmitted()
                + "', `track_record_point` = '" + op.getTrack_record_point()
                + "' WHERE outsource_project_id = '" + op.getProject_id() + "';";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static ArrayList<OutsourceProject> retrieveAll() {
        String sql = "SELECT `outsource_project_id`, `project_id`, `man_hours_dot_net`, "
                + "`man_hours_c_sharp`, `man_hours_jsp`, `man_hours_php`, `man_hours_ruby`, "
                + "`man_hours_mysql`, `man_hours_mssql`, `man_hours_oracle`, `price`, "
                + "`due_turn`, `offered_by`, `taken_by`, `listing_turn_remaining`, `submitted`, `track_record_point` "
                + "FROM `outsource_project` WHERE taken_by=0 OR taken_by is NULL";
        DB db = new DB();
        db.connect();
        db.query(sql);

        ArrayList<OutsourceProject> opList = new ArrayList<OutsourceProject>();
        while (db.next()) {
            OutsourceProject op = populate(db);
            opList.add(op);
        }
        db.close();
        return opList;
    }

    public static ArrayList<OutsourceProject> retrievePlayerProjects(int player_id) {
        String sql = "SELECT `outsource_project_id`, `project_id`, `man_hours_dot_net`, "
                + "`man_hours_c_sharp`, `man_hours_jsp`, `man_hours_php`, `man_hours_ruby`, "
                + "`man_hours_mysql`, `man_hours_mssql`, `man_hours_oracle`, `price`, "
                + "`due_turn`, `offered_by`, `taken_by`, `listing_turn_remaining`, `submitted`, `track_record_point` "
                + "FROM `outsource_project` WHERE taken_by=" + player_id + " AND submitted=0";
        DB db = new DB();
        db.connect();
        db.query(sql);

        ArrayList<OutsourceProject> opList = new ArrayList<OutsourceProject>();
        while (db.next()) {
            OutsourceProject op = populate(db);
            opList.add(op);
        }
        db.close();
        return opList;
    }

    public static ArrayList<OutsourceProject> retrieveAllOutsourceProjectOfProject(int project_id) {

        String sql = "SELECT `outsource_project_id`, `project_id`, `man_hours_dot_net`, "
                + "`man_hours_c_sharp`, `man_hours_jsp`, `man_hours_php`, `man_hours_ruby`, "
                + "`man_hours_mysql`, `man_hours_mssql`, `man_hours_oracle`, `price`, "
                + "`due_turn`, `offered_by`, `taken_by`, `listing_turn_remaining`, `submitted`, `track_record_point` "
                + "FROM `outsource_project` WHERE project_id = '" + project_id + "'";
        DB db = new DB();
        db.connect();
        db.query(sql);

        ArrayList<OutsourceProject> opList = new ArrayList<OutsourceProject>();
        while (db.next()) {
            OutsourceProject op = populate(db);
            opList.add(op);
        }
        db.close();
        return opList;
    }

    public static void deleteOutsourceProject(int project_id){
        String sql = "DELETE FROM outsource_project WHERE outsource_project_id=" +project_id;
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static void clear() {
        String sql = "TRUNCATE TABLE outsource_project";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }
}
